/**
 * 
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import model.Administrator;
import model.Door;
import model.Log;
import model.Message;
import model.User;
import service.SecurityQueryHelper;
import utils.DBUtils;
import exception.DataAccessException;

/**
 * 
 * @author yalin
 * 
 */
public class SecurityDaoImpl implements SecurityDao {
	// Door
	private static final String UPDATE_DOOR = "update door set status=? where door_id=?";
	private static final String GET_DOOR_BYID = "select * from door where door_id=?";
	private static final String UPDATE_All = "update door set status=?";
	private static final String LOAD_ALLDOOR = "select * from door order by door_id desc";
	// Log
	private static final String CREATE_LOG = "insert into log values(null,?,?,?)";
	// Message
	private static final String GET_MSG_BYID = "select * from message where msg_id=?";
	private static final String UPDATE_MSG = "update message set receiver_id=? where msg_id=?";

	@Override
	public void updateDoor(Door door) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(UPDATE_DOOR);
			pstmt.setInt(1, 1);
			pstmt.setString(2, door.getDoor_ID());
			pstmt.executeUpdate();
			System.out.println("Update door " + door.getDoor_ID() + ".");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, null);
		}

	}

	@Override
	public void updateAll() {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(UPDATE_All);
			pstmt.setInt(1, 1);
			pstmt.executeUpdate();
			System.out.println("Update all doors. ");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, null);
		}

	}

	@Override
	public Door getDoorByID(String doorID) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Door door = null;

		try {
			pstmt = conn.prepareStatement(GET_DOOR_BYID);
			pstmt.setString(1, doorID);
			rset = pstmt.executeQuery();

			if (rset.next()) {
				door = new Door();
				door.setDoor_ID(doorID);
				door.setStatus(rset.getInt("status"));
				door.setDescription(rset.getString("description"));
				door.setArea_ID(rset.getInt("area_id"));
				System.out.println("Get door : " + doorID);
			} else {
				throw new DataAccessException("Door " + doorID
						+ " is not existed");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}

		return door;
	}

	private String generateSQL(SecurityQueryHelper helper) {
		String baseSQL = "";
		if (helper.getType().equals("log")) {
			baseSQL = "select * from log where 1=1 ";
			if (StringUtils.isNotEmpty(helper.getDoorID())) {
				baseSQL += " and log_content like '%" + helper.getDoorID()
						+ "%'";
			}
			if (StringUtils.isNotEmpty(helper.getOperator())) {
				baseSQL += " and operator = '" + helper.getOperator() + "'";
			}
			baseSQL += " order by log_id desc";
		}
		if (helper.getType().equals("message")) {
			baseSQL = "select * from message where 1=1 and msg_type='"
					+ helper.getMsg_type() + "' ";
			if (helper.getMsg_status() != null) {
				baseSQL += " and msg_status =" + helper.getMsg_status();
			}
			baseSQL += " order by msg_date desc";

		}
		System.out.println("Generate SQL : " + baseSQL);
		return baseSQL;
	}

	@Override
	public Integer count(SecurityQueryHelper helper) {
		String sql = this.generateSQL(helper);

		sql = sql.replace("*", "count(*) count");
		sql = sql.substring(0, sql.indexOf("order")).trim();

		System.out.println("count: " + sql);

		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int count = 0;

		try {

			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();

			if (rset.next()) {
				count = rset.getInt("count");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}
		return count;
	}

	@Override
	public List<Log> getScopedLogs(SecurityQueryHelper helper, int begin,
			int end) {
		String sql = this.generateSQL(helper);
		sql += " limit ?,?";
		System.out.println("get scoped logs:" + sql + "," + begin + "," + end);

		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<Log> logList = null;

		try {

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, begin-1);
			pstmt.setInt(2, 3);
			rset = pstmt.executeQuery();
			logList = new ArrayList<Log>();

			while (rset.next()) {
				Log log = new Log();
				log.setLog_ID(rset.getInt("log_id"));
				log.setLog_Date(rset.getString("log_date"));
				log.setLog_Content(rset.getString("log_content"));
				log.setOperator(rset.getString("operator"));
				logList.add(log);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}
		return logList;

	}

	@Override
	public List<Door> loadAllDoor() {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<Door> doorList = null;

		try {
			pstmt = conn.prepareStatement(LOAD_ALLDOOR);
			rset = pstmt.executeQuery();

			doorList = new ArrayList<Door>();
			while (rset.next()) {
				Door door = new Door();
				door.setDoor_ID(rset.getString("door_id"));
				door.setArea_ID(rset.getInt("area_id"));
				door.setStatus(rset.getInt("status"));
				door.setDescription(rset.getString("description"));

				doorList.add(door);
			}
			System.out.println("Get all doors");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}

		return doorList;
	}

	@Override
	public void createLog(Log log, Door door) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(CREATE_LOG);
			pstmt.setString(1, log.getLog_Date());
			pstmt.setString(2, log.getLog_Content());
			pstmt.setString(3, log.getOperator());
			pstmt.executeUpdate();
			System.out.println("Create a new log!");
		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, null);

		}

	}

	@Override
	public List<Message> getScopedMsgs(SecurityQueryHelper helper, int begin,
			int end) {
		String sql = this.generateSQL(helper);
		sql += " limit ?,?";
		System.out.println("get scoped Msgs:" + sql + "," + begin + "," + end);

		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<Message> msgList = null;

		try {

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, begin-1);
			pstmt.setInt(2, 3);
			rset = pstmt.executeQuery();
			msgList = new ArrayList<Message>();

			while (rset.next()) {
				Message msg = new Message();
				msg.setMsg_ID(rset.getInt("msg_id"));
				msg.setSender_ID(rset.getString("sender_id"));
				msg.setMsg_Status(rset.getInt("msg_status"));
				msg.setMsg_Content(rset.getString("msg_content"));
				msg.setMsg_Date(rset.getString("msg_date"));
				msg.setReceiver_ID(rset.getString("receiver_id"));
				msg.setMsg_Type(rset.getString("msg_type"));
				msgList.add(msg);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}
		return msgList;
	}

	@Override
	public Message getMsgByID(Integer msg_ID) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Message msg = null;

		try {
			pstmt = conn.prepareStatement(GET_MSG_BYID);
			pstmt.setInt(1, msg_ID);
			rset = pstmt.executeQuery();

			if (rset.next()) {
				msg = new Message();
				msg.setMsg_ID(rset.getInt("msg_id"));
				msg.setSender_ID(rset.getString("sender_id"));
				msg.setMsg_Status(rset.getInt("msg_status"));
				msg.setMsg_Content(rset.getString("msg_content"));
				msg.setMsg_Date(rset.getString("msg_date"));
				msg.setReceiver_ID(rset.getString("receiver_id"));
				msg.setMsg_Type(rset.getString("msg_type"));
				System.out.println("Get messsge : " + msg_ID);
			} else {
				throw new DataAccessException();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}

		return msg;
	}

	@Override
	public void updateMsg(Message msg) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(UPDATE_MSG);
			pstmt.setString(1, msg.getReceiver_ID());
			pstmt.setInt(2, msg.getMsg_ID());
			pstmt.executeUpdate();
			System.out.println("Update message status.");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, null);
		}

	}

}
